﻿#-*- encoding : utf-8 -*-
from DBUtils.PooledDB import PooledDB
import MySQLdb

import time,hashlib,datetime
from PyEmail import *
import logging
#,unix_socket='/var/lib/mysql/mysql.sock'
class ConnectMySQL(object):
    def __init__(self,host='localhost',user='root',password='',db='',port=3306,charset='utf8',unix_socket='/var/lib/mysql/mysql.sock'):
        #self.pool = PooledDB(MySQLdb,5,host="127.0.0.1",user="root",passwd="ipsec123",db="ExpressDemo",charset="utf8")
        if unix_socket!=None:
            self.pool = PooledDB(MySQLdb,1,host=host,port=port,user=user,passwd=password,db=db,charset=charset,unix_socket=unix_socket)
        else:
            self.pool = PooledDB(MySQLdb,1,host=host,port=port,user=user,passwd=password,db=db,charset=charset)
    def conv_tuple_to_str(self,alist=[]):
        if len(alist)==0:
            return ''
        
        if len(alist)==1:
            return "%s"%alist[0]
        blist = []
        for a in alist:
            blist.append(str(a))
        return '"'+'","'.join(blist)+'"'
    def selectCount(self,query):
        db = self.pool.connection()
        cur = db.cursor()
        count = cur.execute(query)
        cur.close()
        db.commit()
        db.close()
        return count    
    def SelectCount(self,query):
        db = self.pool.connection()
        cur = db.cursor()
        cur.execute(query)
        result = cur.fetchone()
        if result:
            count = int(result[0])
        else:count = 0
        cur.close()
        db.commit()
        db.close()
        return count    
    def SelectAll(self,query):
        db = self.pool.connection()
        cur = db.cursor()
        count = cur.execute(query)
        if count==0:return []
        result = cur.fetchall()
        db.commit()
        cur.close()
        db.close()
        if result==None:result=[]
        return result
    def SelectOne(self,query):
        db = self.pool.connection()
        cur = db.cursor()
        count = cur.execute(query)
        if count==0:return None
        result = cur.fetchone()
        db.commit()
        cur.close()
        db.close()
        return result  
    def execute(self,query):
        try:
            db = self.pool.connection()
            cur = db.cursor()
            count = cur.execute(query)
            db.commit()
            cur.close()
            db.close()
            return True
        except Exception,error:
            logging.error( error)
            return False
    
        
    def descTab(self,tab):
        fields = []
        db = self.pool.connection()
        cur = db.cursor()
        cur.execute('desc %s'%tab)
        results = cur.fetchall()        
        for rs in results:
            fields.append(rs[0])
        db.commit()
        cur.close()
        db.close()
        return fields    
    def exists(self,tab,args):
        db = self.pool.connection()
        cur = db.cursor()            
        query = ''

        
    def updateTab(self,tab,attrs):
        pass


    
    
    def executeAll(self,queryList):
        try:
            db = self.pool.connection()
            cur = db.cursor()   
        except Exception,error:
            return {'status':0,'msg':error}
        try:
            for query in queryList:
                cur.execute(query)
        except Exception,error:
            cur.execute("ROLLBACK")
            cur.close()
            db.commit()
            db.close()
            return {'status':0,'msg':error}
        cur.close()
        db.commit()
        db.close()
        return {'status':888,'msg':'成功'}
    
    def executemany(self,sql,qlist):
        try:
            db = self.pool.connection()
            cur = db.cursor()
            count = cur.executemany(sql,qlist)
            db.commit()
            cur.close()
            db.close()
            return {'status':1}
        except Exception,error:
            print error
            return {'status':0,'msg':error}
        
        
        
if  __name__=='__main__':
    myConnectMySQL = ConnectMySQL('localhost','root','zhimatech','zmexport',unix_socket=None)
    sql = 'insert into group_count_by_rssi (day,hour,dgroup,rssi) values(%s,%s,%s,%s)'
    
    qlist = [('2015-09-16',1,None,-90)]
    myConnectMySQL.executemany(sql, qlist)
    
    
    query = 'select day as day,hour as hour,rssi as rssi from group_count_by_rssi'
    print myConnectMySQL.SelectAll(query)
    